﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

using ISS.CaseStudy.DBUtility;
using ISS.CaseStudy.Entities;

namespace ISS.CaseStudy.DAL
{
    public static class RelatedInfoDLL
    {
        public static DataSet GetAllInfos()
        {
            string sql = "select * from RelatedInfo order by ModifyTime desc";
            DataSet dataSet = Database.SpExecuteDataSet(sql);
            return dataSet;
        }

        public static DataSet GetAllInfosByGolbal()
        {
            string sql = "select * from RelatedInfo order by IsGlobal desc, ModifyTime desc";
            DataSet dataSet = Database.SpExecuteDataSet(sql);
            return dataSet;
        }

        public static RelatedInfo GetInfo(string id)
        {
            string sql = "select * from RelatedInfo where [Id]=@id";
            SqlParameter[] paramCollection = { new SqlParameter("@id", id) };
            DataSet dataSet = Database.SpExecuteDataSet(sql, paramCollection);
            DataTable dataTable = dataSet.Tables[0];
            if (dataTable.Rows.Count == 0)
            {
                return null;
            }

            DataRow row = dataTable.Rows[0];

            RelatedInfo relatedInfo = new RelatedInfo();
            relatedInfo.Id = id;
            relatedInfo.Title = row["Title"].ToString();
            relatedInfo.Url = row["Url"].ToString();
            relatedInfo.IsCapability = row["Capabilities"].ToString().Equals("1") ? true : false;
            relatedInfo.IsIndustryFocus = row["IndustryFocus"].ToString().Equals("1") ? true : false;
            relatedInfo.IsGlobal = row["IsGlobal"].ToString().Equals("1") ? true : false;
            relatedInfo.Category = row["Category"].ToString();

            if (!string.IsNullOrEmpty(row["CreateTime"].ToString()))
            {
                relatedInfo.CreateTime = DateTime.Parse(row["CreateTime"].ToString());
            }

            if (!string.IsNullOrEmpty(row["ModifyTime"].ToString()))
            {
                relatedInfo.ModifyTime = DateTime.Parse(row["ModifyTime"].ToString());
            }

            return relatedInfo;
        }

        public static bool Delete(string id)
        {
            bool b = false;
            string sql = "delete from RelatedInfo where ([Id]=@Id)";
            SqlParameter[] paramCollection = { new SqlParameter("@Id", id) };
            int i = Database.SpExecuteNonQuery(sql, paramCollection);
            if (i > 0)
            {
                b = true;
            }
            return b;
        }

        public static bool Insert(RelatedInfo relatedInfo, out string errorDescription, out int id)
        {
            string errorCode = string.Empty;
            errorDescription = string.Empty;
            id = -1;

            string procedureName = "up_RelatedInfo_Add";
            SqlParameter errorCodeParam = new SqlParameter("@ErrorCode", SqlDbType.VarChar, 2, errorCode);
            errorCodeParam.Direction = ParameterDirection.Output;

            SqlParameter errorDescriptionParam = new SqlParameter("@ErrorDescription", SqlDbType.VarChar, 50, errorDescription);
            errorDescriptionParam.Direction = ParameterDirection.Output;

            SqlParameter relatedInfoIdParam = new SqlParameter("@NewRelatedInfoID", id);
            relatedInfoIdParam.Direction = ParameterDirection.Output;

            SqlParameter[] paramCollection = { new SqlParameter("@Title", relatedInfo.Title), 
                                                 new SqlParameter("@Url", relatedInfo.Url), 
                                                 new SqlParameter("@IsCapability", relatedInfo.IsCapability ? "1" : "0"),
                                                 new SqlParameter("@IsIndustryFocus", relatedInfo.IsIndustryFocus ? "1" : "0"),
                                                 new SqlParameter("@IsGlobal", relatedInfo.IsGlobal ? "1" : "0"),
                                                 new SqlParameter("@Category", relatedInfo.Category),
                                                 new SqlParameter("@CaseStudies", relatedInfo.CaseStudies),
                                                 errorCodeParam,
                                                 errorDescriptionParam,
                                                 relatedInfoIdParam
                                             };

            Database.ExecuteNonQuery(procedureName, paramCollection);

            errorCode = errorCodeParam.Value.ToString();
            errorDescription = errorDescriptionParam.Value.ToString();
            id = int.Parse(relatedInfoIdParam.Value.ToString());

            if (errorCode != "00")
            {
                return false;
            }

            return true;
        }

        public static bool Update(RelatedInfo relatedInfo, out string errorDescription)
        {
            errorDescription = string.Empty;
            string errorCode = string.Empty;
            string procedureName = "up_RelatedInfo_Update";
            SqlParameter errorCodeParam = new SqlParameter("@ErrorCode", SqlDbType.VarChar, 2, errorCode);
            errorCodeParam.Direction = ParameterDirection.Output;

            SqlParameter errorDescriptionParam = new SqlParameter("@ErrorDescription", SqlDbType.VarChar, 50, errorDescription);
            errorDescriptionParam.Direction = ParameterDirection.Output;

            SqlParameter[] paramCollection = { new SqlParameter("@Id", relatedInfo.Id),
                                                 new SqlParameter("@Title", relatedInfo.Title), 
                                                 new SqlParameter("@Url", relatedInfo.Url), 
                                                 new SqlParameter("@IsCapability", relatedInfo.IsCapability ? "1" : "0"),
                                                 new SqlParameter("@IsIndustryFocus", relatedInfo.IsIndustryFocus ? "1" : "0"),
                                                 new SqlParameter("@IsGlobal", relatedInfo.IsGlobal),
                                                 new SqlParameter("@Category", relatedInfo.Category),
                                                 new SqlParameter("@CaseStudies", relatedInfo.CaseStudies),
                                                 errorCodeParam,
                                                 errorDescriptionParam
                                             };

            Database.ExecuteNonQuery(procedureName, paramCollection);

            errorCode = errorCodeParam.Value.ToString();
            errorDescription = errorDescriptionParam.Value.ToString();

            if (errorCode != "00")
            {
                return false;
            }

            return true;
        }
    }
}
